package com.example.fbi_text.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.fbi_text.R;
import com.example.fbi_text.bean.News;
import com.example.fbi_text.bean.Product;
import com.example.fbi_text.bean.ProductCart;
import com.example.fbi_text.bean.User;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {
    private static final String TAG = "DBHelper";
    private static final String DATABASE_NAME = "db_jn_news";// 数据库名
    private static final int DATABASE_VERSION = 5;// 数据库版本号
    private static final String TABLE_NAME_USERS = "users"; //用户表
    private static final String TABLE_NAME_NEWS = "NEWS"; //新闻表
    private static final String TABLE_NAME_SHOPPING = "shopping"; //商品表
    private static final String TABLE_NAME_SHOPPING_COURSE = "shopping_course"; // 关联表 关联商品和购物车
    private static final String TABLE_NAME_SETTINGS = "settings"; // 设置表

    private static DBHelper mHelper = null;

//    创建数据库
    public DBHelper(Context context){
        super(context,DATABASE_NAME,null,DATABASE_VERSION);
    }

    //单例模式
    public static DBHelper getInstance(Context context){
        if(mHelper == null){
            mHelper = new DBHelper(context);
        }
        return mHelper;
    }

//    数据库创建时候调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        initTable(db);
        initData(db);
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 5) {
            // 添加quantity列到购物车表
            db.execSQL("ALTER TABLE " + TABLE_NAME_SHOPPING_COURSE +
                    " ADD COLUMN shopping_cart_quantity INTEGER DEFAULT 1");
        }
    }

    private void initTable(SQLiteDatabase db){
        // 创建用户表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USERS + " (" +
                "User_id integer primary key autoincrement," +
                "User_name text UNIQUE," +
                "User_password text," +
                "User_email text UNIQUE)");
        // 创建新闻表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_NEWS + " (" +
                "news_id integer primary key autoincrement," +
                "news_title text," +
                "news_content text," +
                "news_author text," +
                "news_time text)");
        // 创建商品表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SHOPPING + " (" +
                "shopping_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "shopping_name  TEXT," +
                "shopping_price  TEXT," +
                "shopping_content TEXT,"+
                "FOREIGN KEY (shopping_id) REFERENCES SHOPPING_CART(shopping_cart_id))");
        //创建购物车
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SHOPPING_COURSE + " (" +
                "shopping_cart_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "shopping_cart_name  TEXT," +
                "shopping_cart_price  TEXT," +
                "shopping_cart_description  TEXT," +
                "shopping_cart_reviews TEXT," +
                "shopping_cart_quantity INTEGER DEFAULT 1)");  // 新增数量字段

    }
    /**
     * 初始化数据库内部模拟数据(仅在第一次安装时执行)
     *
     * @param db 数据库
     */
    private void initData(SQLiteDatabase db) {
        db.execSQL("insert into " + TABLE_NAME_USERS +
                " (User_id , User_name, User_password, User_email) " +
                "values" +
                "(1, 'zhangsan', '123456', '12345678@qq.com')");
        db.execSQL("insert into " + TABLE_NAME_USERS +
                " (User_id , User_name, User_password, User_email) " +
                "values" +
                "(2, 'lisi', '321654', '123684@qq.com')");
        db.execSQL("insert into " + TABLE_NAME_USERS +
                " (User_id , User_name, User_password, User_email) " +
                "values" +
                "(3, 'lisa', '789465', '645987@qq.com')");
        db.execSQL("insert into " + TABLE_NAME_USERS +
                " (User_id , User_name, User_password, User_email) " +
                "values" +
                "(4, 'lilei', '147258', '741952@qq.com')");
        db.execSQL("insert into " + TABLE_NAME_USERS +
                " (User_id , User_name, User_password, User_email) " +
                "values" +
                "(5, '1', '1', '1@qq.com')");




        db.execSQL("insert into " + TABLE_NAME_NEWS +
                " (news_title , news_content, news_author, news_time) " +
                "values" +
                "('android', '11111', '张老师', '2025/4/29')");
        db.execSQL("insert into " + TABLE_NAME_NEWS +
                " (news_title , news_content, news_author, news_time) " +
                "values" +
                "('lkj', '222', '老师', '2025/4/29')");
        db.execSQL("insert into " + TABLE_NAME_NEWS +
                " (news_title , news_content, news_author, news_time) " +
                "values" +
                "('sdf', '333', '环境', '2025/4/29')");
        db.execSQL("insert into " + TABLE_NAME_NEWS +
                " (news_title , news_content, news_author, news_time) " +
                "values" +
                "('qwer', '444', 'iu哦iu有害健康', '2025/4/29')");
        db.execSQL("insert into " + TABLE_NAME_NEWS +
                " (news_title , news_content, news_author, news_time) " +
                "values" +
                "('ghjk', '555', 'li老师', '2025/4/29')");



        db.execSQL("insert into " + TABLE_NAME_SHOPPING +
                " (shopping_id,shopping_name, shopping_price,shopping_content)"+
                "values" +
                "(001,'猫咪','5000','可爱的猫咪幼崽')"
        );
        db.execSQL("insert into " + TABLE_NAME_SHOPPING +
                " (shopping_id,shopping_name, shopping_price,shopping_content)"+
                "values" +
                "(002,'茶几','3000','新中式木制茶几')"
        );
        db.execSQL("insert into " + TABLE_NAME_SHOPPING +
                " (shopping_id,shopping_name, shopping_price,shopping_content)"+
                "values" +
                "(003,'香水','2000','气质女神香水')"
        );
        db.execSQL("insert into " + TABLE_NAME_SHOPPING +
                " (shopping_id,shopping_name, shopping_price,shopping_content)"+
                "values" +
                "(004,'手机','4000','手机')"
        );

//        添加数据在购物车中
        db.execSQL("insert into " + TABLE_NAME_SHOPPING_COURSE +
                " (shopping_cart_id,shopping_cart_name, shopping_cart_price,shopping_cart_description,shopping_cart_reviews,shopping_cart_quantity)"+
                "values" +
                "(003,'香水','2000','气质女神香水','13000',1)"
        );

    }

    //添加用户
    public void insertUser(User user) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into " + TABLE_NAME_USERS +
                " (User_name , User_password, User_email) " +
                "values" +
                "('" + user.getUser_name() + "','" + user.getUser_password() + "','" + user.getUser_email() + "')");
    }

    public int insertUser2(User user) {
        //获取可写数据库
        SQLiteDatabase db = getWritableDatabase();

        //创建ContentValues以Key-Value的形式封装数据
        ContentValues values = new ContentValues();
        values.put("User_name", user.getUser_name());
        values.put("User_password", user.getUser_password());
        values.put("User_email", user.getUser_email());

        //执行insert方法，插入数据
        try {
            long newRowId = db.insertOrThrow(TABLE_NAME_USERS, null, values);
            Log.d(TAG, "insertUser2 newRowId = " + newRowId);
            return (int) newRowId;
        } catch (SQLiteConstraintException e) {
            Log.e(TAG, "插入用户失败，违反唯一约束", e);
            return -1;
        }
    }

    //根据名字删除
    public void deleteUserByName(String name) {
        if (name != null) {
            SQLiteDatabase db = getReadableDatabase();
            int changedRowCount = db.delete(TABLE_NAME_USERS, "User_name = ?", new String[]{name});
            Log.d(TAG, "deleteStudentByName changedRowCount = " + changedRowCount);
        }
    }

    //根据id删除
    public void deleteUserById(int id) {
        if (id >= 0) {
            SQLiteDatabase db = getReadableDatabase();
            int changedRowCount = db.delete(TABLE_NAME_USERS, "User_id = ?", new String[]{String.valueOf(id)});
            Log.d(TAG, "deleteStudentById changedRowCount = " + changedRowCount);
        }
    }
    //根据id修改名字
    public void updateStudentNameById(String name, int id) {
        if (name != null && id >= 0) {
            SQLiteDatabase db = getReadableDatabase();
            ContentValues values = new ContentValues();
            values.put("User_name", name);
            int changedRowCount = db.update(TABLE_NAME_USERS, values, "User_id = ?", new String[] {String.valueOf(id)});
            Log.d(TAG, "updateStudentNameById changedRowCount = " + changedRowCount);
        }
    }
    //根据id修改title
    public void updateNewsNameById(String title, int id) {
        if (title != null && id >= 0) {
            SQLiteDatabase db = getReadableDatabase();
            ContentValues values = new ContentValues();
            values.put("news_title", title);
            int changedRowCount = db.update(TABLE_NAME_NEWS, values, "news_id = ?", new String[] {String.valueOf(id)});
            Log.d(TAG, "updateNewsNameById changedRowCount = " + changedRowCount);
        }
    }
//修改密码
    public boolean updateUserPassword(int userId, String newPassword) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("User_password", newPassword);

        int rowsAffected = db.update(
                TABLE_NAME_USERS, // 表名
                values,
                "User_id = ?", // WHERE条件
                new String[]{String.valueOf(userId)});

        db.close();
        return rowsAffected > 0;
    }

    //查询所有
    @SuppressLint("Range")
    public List<User> selectAllStudent() {
        List<User> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_NAME_USERS,     // 表名
                null,                   // 要查询的列数据
                null,                   // 条件语句
                null,                   // 条件语句的值
                null,                   // group by
                null,                   // having
                null                    // 排序语句
        );
        if (cursor != null && cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                User user = new User();
                user.setUser_id(cursor.getInt(cursor.getColumnIndex("User_id")));
                user.setUser_name(cursor.getString(cursor.getColumnIndex("User_name")));
                user.setUser_password(cursor.getString(cursor.getColumnIndex("User_password")));
                user.setUser_email(cursor.getString(cursor.getColumnIndex("User_email")));
                list.add(user);
            }
        }
        Log.d(TAG, "selectAllStudent = " + list.size());
        return list;
    }
    //登录
    @SuppressLint("Range")
    public User login(String user_name, String user_email, String user_password) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = null;
        User user = null;

        try {
            cursor = db.query(
                    TABLE_NAME_USERS, // 表名
                    null, // 要查询的列数据
                    "User_name = ? And User_email = ? And User_password = ?", // 条件语句
                    new String[]{user_name, user_email, user_password}, // 条件语句的值
                    null, // group by
                    null, // having
                    null // 排序语句
            );

            if (cursor != null && cursor.getCount() > 0) {
                if (cursor.moveToFirst()) {
                    user = new User();
                    user.setUser_id(cursor.getInt(cursor.getColumnIndex("User_id")));
                    user.setUser_name(cursor.getString(cursor.getColumnIndex("User_name")));
                    user.setUser_password(cursor.getString(cursor.getColumnIndex("User_password")));
                    user.setUser_email(cursor.getString(cursor.getColumnIndex("User_email")));
                    Log.d(TAG, "login = " + user.toString());
                }
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }

        return user;
    }


    //根据id查询信息
    @SuppressLint("Range")
    public User getUserById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = null;
        User user = null;

        try {
            cursor = db.query(
                    TABLE_NAME_USERS, // 表名
                    null, // 要查询的列数据
                    "User_id = ?", // 条件语句
                    new String[]{String.valueOf(id)}, // 条件语句的值
                    null, // group by
                    null, // having
                    null // 排序语句
            );

            if (cursor != null && cursor.getCount() > 0) {
                if (cursor.moveToFirst()) {
                    user = new User();
                    user.setUser_id(cursor.getInt(cursor.getColumnIndex("User_id")));
                    user.setUser_name(cursor.getString(cursor.getColumnIndex("User_name")));
                    user.setUser_password(cursor.getString(cursor.getColumnIndex("User_password")));
                    user.setUser_email(cursor.getString(cursor.getColumnIndex("User_email")));
                    Log.d(TAG, "current student = " + user.toString());
                }
            }
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            cursor.close();
        }
        db.close();
        return user;
    }
//      根据id查询新闻
    @SuppressLint("Range")
    public News getNewsById(int id) {
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_NAME_NEWS,                                                        // 表名
                null,                                                                      // 要查询的列数据
                "news_id = ?",                                                                 // 条件语句
                new String[] {String.valueOf(id)},                                         // 条件语句的值
                null,                                                                      // group by
                null,                                                                      // having
                null                                                                       // 排序语句
        );
        News news = null;
        if (cursor != null && cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                news = new News();
                news.setNews_id(cursor.getInt(cursor.getColumnIndex("news_id")));
                news.setNews_title(cursor.getString(cursor.getColumnIndex("news_title")));
                news.setNews_content(cursor.getString(cursor.getColumnIndex("news_content")));
                news.setNews_author(cursor.getString(cursor.getColumnIndex("news_author")));
                news.setNews_time(cursor.getString(cursor.getColumnIndex("news_time")));

                Log.d(TAG, "current News = " + news.toString());
            }
        }

        return news;
    }
    //添加新闻
    public void insertNews(News news) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into " + TABLE_NAME_NEWS +
                " (news_id , news_title , news_content , news_author , news_time ) " +
                "values" +
                "('" +news.getNews_id() + "','" +news.getNews_title() + "','" + news.getNews_content()+ "','" + news.getNews_author() + "','" + news.getNews_time() + "')");
    }
    //添加文章（新闻）
    public int insertNews2(News news){
        //获取可写数据库
        SQLiteDatabase db=getWritableDatabase();
        //创建ContentValues以Key-Value的形式封装数据
        ContentValues values=new ContentValues();
        values.put("news_title",news.getNews_title());
        values.put("news_author",news.getNews_author());
        values.put("news_time",news.getNews_time());
        values.put("news_content",news.getNews_content());

        //执行insert方法，插入数据
        long newRowID=db.insert(TABLE_NAME_NEWS,null,values);
        Log.d(TAG,"insertNews2 newRowID = "+newRowID);
        return (int) newRowID;

    }

    //查询所有新闻
    @SuppressLint("Range")
    public List<News> selectAllNews() {
        List<News> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_NAME_NEWS,     // 表名
                null,                   // 要查询的列数据
                null,                   // 条件语句
                null,                   // 条件语句的值
                null,                   // group by
                null,                   // having
                null                    // 排序语句
        );

        if (cursor != null && cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                News news = new News();
                news.setNews_id(cursor.getInt(cursor.getColumnIndex("news_id")));
                news.setNews_title(cursor.getString(cursor.getColumnIndex("news_title")));
                news.setNews_content(cursor.getString(cursor.getColumnIndex("news_content")));
                news.setNews_author(cursor.getString(cursor.getColumnIndex("news_author")));
                news.setNews_time(cursor.getString(cursor.getColumnIndex("news_time")));
                list.add(news);
            }
        }
        Log.d(TAG, "selectAllNews count = " + list.size());
        return list;
    }
    // 根据id更新新闻所有字段
    public boolean updateNewsById(int id, String title, String author, String time, String content) {
        Log.d(TAG, "尝试更新新闻: ID=" + id);
        if (id >= 0) {
            SQLiteDatabase db = getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put("news_title", title);
            values.put("news_author", author);
            values.put("news_time", time);
            values.put("news_content", content);

            int changedRowCount = db.update(TABLE_NAME_NEWS, values, "news_id = ?", new String[]{String.valueOf(id)});
            db.close();
            Log.d(TAG, "更新结果: " + (changedRowCount > 0));
            return changedRowCount > 0;
        }
        return false;
    }


    //    删除
    public void deleteNewsById(int id) {
        if (id >= 0) {
            SQLiteDatabase db = getReadableDatabase();
            int changedRowCount = db.delete(TABLE_NAME_NEWS, "News_id = ?", new String[]{String.valueOf(id)});
            Log.d(TAG, "deleteStudentById changedRowCount = " + changedRowCount);
        }
    }
    /*
     * 初始化商品表数据*/
//    添加商品
    public  void  insertGoods(Product product){
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL("insert into "+TABLE_NAME_SHOPPING+
                " (shopping_id,shopping_name, shopping_price,shopping_content)"+
                "values"+
                "('"+product.getId()+"+','"+product.getName()+"','"+product.getPrice()+"'," +
                "'"+product.getSales()+"','"+product.getReviews()+"'+'"+product.getDescription()+"')"
        );
    }

    public int insertGoods2(Product product) {
        // 获取可写数据库
        SQLiteDatabase db = getWritableDatabase();
        // 创建 ContentValues 以 Key-Value 的形式封装数据
        ContentValues values = new ContentValues();
        values.put("shopping_name", product.getName());
        values.put("shopping_price", product.getPrice());
        values.put("shopping_content", product.getDescription());

        // 执行 insert 方法，插入数据
        long newRowID = db.insert(TABLE_NAME_SHOPPING, null, values);
        Log.d(TAG, "insertGoods2 newRowID = " + newRowID);

        db.close();
        return (int) newRowID;
    }
    //    根据商品id下架商品
    public void deletGoodsById(String id){
        if (id!=null){
            SQLiteDatabase db=getReadableDatabase();
            int changeRowCount= db.delete(TABLE_NAME_SHOPPING,"shopping_id = ?",new String[]{String.valueOf(id)});
            Log.d(TAG, "deletGoodsById changedRowCount: "+changeRowCount);
        }
    }
    //查询所有商品
    @SuppressLint("Range")
    public List<Product> getAllProducts() {
        List<Product> productList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(
                TABLE_NAME_SHOPPING,
                new String[]{"shopping_id", "shopping_name", "shopping_price","shopping_content"},
                null, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndex("shopping_id")));
                product.setName(cursor.getString(cursor.getColumnIndex("shopping_name")));

                // 检查价格是否为 null
                String price = cursor.getString(cursor.getColumnIndex("shopping_price"));
                product.setPrice(price != null ? price : "0.00"); // 默认值
                product.setDescription(cursor.getString(cursor.getColumnIndex("shopping_content")));

                // 根据商品ID设置对应的图片资源
                switch(product.getId()) {
                    case 001:
                        product.setImageResId(R.mipmap.cat);
                        break;
                    case 002:
                        product.setImageResId(R.mipmap.table);
                        break;
                    case 003:
                        product.setImageResId(R.mipmap.perfume);
                        break;
                    case 004:
                        product.setImageResId(R.mipmap.phone);
                        break;
                    default:
                        product.setImageResId(R.mipmap.cat);
                }

                productList.add(product);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return productList;
    }
    /**
     * 检查用户名是否已存在
     */
    public boolean isUsernameExists(String username) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = null;
        try {
            cursor = db.query(
                    TABLE_NAME_USERS,
                    new String[]{"User_id"},
                    "User_name = ?",
                    new String[]{username},
                    null, null, null);
            return cursor != null && cursor.getCount() > 0;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }

    /**
     * 检查邮箱是否已存在
     */
    public boolean isEmailExists(String email) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = null;
        try {
            cursor = db.query(
                    TABLE_NAME_USERS,
                    new String[]{"User_id"},
                    "User_email = ?",
                    new String[]{email},
                    null, null, null);
            return cursor != null && cursor.getCount() > 0;
        } finally {
            if (cursor != null) {
                cursor.close();
            }
        }
    }

    //    将商品添加到数据库
    public void insertGoodsCart(String name, String price, String description, String reviews) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("shopping_cart_name", name);
        values.put("shopping_cart_price", price);
        values.put("shopping_cart_description", description);
        values.put("shopping_cart_reviews", reviews);
        values.put("shopping_cart_quantity", 1); // 默认数量为1
        db.insert(TABLE_NAME_SHOPPING_COURSE, null, values);
    }

    // 新增更新商品数量方法
    public boolean updateCartItemQuantity(int cartId, int newQuantity) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("shopping_cart_quantity", newQuantity);

        int rowsAffected = db.update(
                TABLE_NAME_SHOPPING_COURSE,
                values,
                "shopping_cart_id = ?",
                new String[]{String.valueOf(cartId)});

        return rowsAffected > 0;
    }
//  查询购物车中所有的数据
@SuppressLint("Range")
public List<ProductCart> getAllCartItems() {
    List<ProductCart> cartItems = new ArrayList<>();
    SQLiteDatabase db = getReadableDatabase();
    Cursor cursor = null;

    try {
        // 明确指定要查询的列
        String[] columns = {
                "shopping_cart_id",
                "shopping_cart_name",
                "shopping_cart_price",
                "shopping_cart_description",
                "shopping_cart_reviews",
                "shopping_cart_quantity"
        };

        cursor = db.query(TABLE_NAME_SHOPPING_COURSE, columns,
                null, null, null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            do {
                ProductCart item = new ProductCart();
                item.setCartID(cursor.getInt(cursor.getColumnIndex("shopping_cart_id")));
                item.setCartName(cursor.getString(cursor.getColumnIndex("shopping_cart_name")));
                item.setCartPrice(cursor.getString(cursor.getColumnIndex("shopping_cart_price")));
                item.setCartDescription(cursor.getString(cursor.getColumnIndex("shopping_cart_description")));
                item.setCartReviews(cursor.getString(cursor.getColumnIndex("shopping_cart_reviews")));

                // 检查quantity列是否存在，避免错误
                int quantityIndex = cursor.getColumnIndex("shopping_cart_quantity");
                if (quantityIndex != -1) {
                    item.setQuantity(cursor.getInt(quantityIndex));
                } else {
                    item.setQuantity(1); // 默认值
                }

                cartItems.add(item);
            } while (cursor.moveToNext());
        }
    } catch (Exception e) {
        Log.e(TAG, "Error getting cart items", e);
    } finally {
        if (cursor != null) {
            cursor.close();
        }
        db.close();
    }
    return cartItems;
}





}
